﻿using Dapper;
using System.Data.SqlClient;
using System.Text;
using yunbo.Model.BI;

namespace yunbo.Repositorys.BIRepositorys
{
	public class KSRepository
	{
		public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User Id=sa;Password=geili@2025;TrustServerCertificate=true;";

		/// <summary>
		/// 获取客商
		/// </summary>
		/// <param name="state"></param>
		/// <param name="str"></param>
		/// <returns></returns>
		public List<KS> GetKS(int state, string? str)
		{
			List<KS> ks = new List<KS>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select [ks_id],[ks_code],[ks_name],
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_type]) ks_type_name,ks_type,ks_jc,
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_level]) ks_level_name,ks_level,
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_sx]) ks_sx_name,ks_sx,
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_hy]) ks_hy_name,ks_hy,
[ks_person],[ks_phone],[ks_cz],[ks_email],[ks_address],
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_gj]) ks_gj_name,ks_gj,ks_state
from bd_ks where [ks_isdelete]=1 and [ks_state]=@state ";

				if (str != null)
				{
					str += @" AND ([ks_code] LIKE @str OR [ks_name] LIKE @str) ";
				}

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					command.Parameters.AddWithValue("@state", state);
					if (str != null)
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							ks.Add(new KS
							{
								ks_id = Convert.ToInt32(reader["ks_id"]),
								ks_code = reader["ks_code"].ToString(),
								ks_name = reader["ks_name"].ToString(),
								ks_type_name = reader["ks_type_name"].ToString(),
								ks_type = Convert.ToInt32(reader["ks_type"]),
								ks_jc = reader["ks_jc"].ToString(),
								ks_level_name = reader["ks_level_name"].ToString(),
								ks_level = Convert.ToInt32(reader["ks_level"]),
								ks_sx_name = reader["ks_sx_name"].ToString(),
								ks_sx = Convert.ToInt32(reader["ks_sx"]),
								ks_hy_name = reader["ks_hy_name"].ToString(),
								ks_hy = Convert.ToInt32(reader["ks_hy"]),
								ks_person = reader["ks_person"].ToString(),
								ks_phone = reader["ks_phone"].ToString(),
								ks_cz = reader["ks_cz"].ToString(),
								ks_email = reader["ks_email"].ToString(),
								ks_address = reader["ks_address"].ToString(),
								ks_gj_name = reader["ks_gj_name"].ToString(),
								ks_gj = Convert.ToInt32(reader["ks_gj"]),
								ks_state = Convert.ToInt32(reader["ks_state"])
							});
						}
					}
				}
			}
			return ks;
		}

		/// <summary>
		/// 获取客商
		/// </summary>
		/// <returns></returns>
		public List<KS> GetOneKS(int? type)
		{
			List<KS> ks = new List<KS>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select [ks_id],ks_code,[ks_name],[ks_jc],
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_level]) ks_level_name,
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[ks_hy]) ks_hy_name 
from bd_ks where [ks_isdelete]=1 and [ks_state]=1 ";

				if (type != null)
				{
					sql += @" and ks_type=@type ";
				}

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{

					if (type != null)
					{
						command.Parameters.AddWithValue("@type", type);
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							ks.Add(new KS
							{
								ks_id = Convert.ToInt32(reader["ks_id"]),
								ks_code = reader["ks_code"].ToString(),
								ks_name = reader["ks_name"].ToString(),
								ks_jc = reader["ks_jc"].ToString(),
								ks_level_name = reader["ks_level_name"].ToString(),
								ks_hy_name = reader["ks_hy_name"].ToString(),
							});
						}
					}
				}
			}
			return ks;
		}

		/// <summary>
		/// 改变状态
		/// </summary>
		/// <param name="id"></param>
		/// <param name="state"></param>
		/// <returns></returns>
		public bool StateChange(int id, int state)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"update [dbo].[bd_ks] set [ks_state]=@State where [ks_id]=@Id";
					return connection.Execute(sql, new { Id = id, State = state }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"状态更改失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 新增
		/// </summary>
		/// <param name="ks"></param>
		/// <returns></returns>
		public bool AddKS(KS ks)
		{
			string sql = @"
            INSERT INTO [dbo].[bd_ks] 
(ks_code, ks_name, ks_type, ks_jc, ks_level, ks_sx, ks_hy, ks_person, ks_phone, ks_cz, ks_email, ks_address, ks_gj, ks_create_by)
values
(@code,@name,@type,@jc,@level,@sx,@hy,@person,@phone,@cz,@email,@address,@gj,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@code", ks.ks_code);
						command.Parameters.AddWithValue("@name", ks.ks_name);
						command.Parameters.AddWithValue("@type", ks.ks_type);
						command.Parameters.AddWithValue("@jc", ks.ks_jc);
						command.Parameters.AddWithValue("@level", ks.ks_level);
						command.Parameters.AddWithValue("@sx", ks.ks_sx);
						command.Parameters.AddWithValue("@hy", ks.ks_hy);
						command.Parameters.AddWithValue("@person", ks.ks_person);
						command.Parameters.AddWithValue("@phone", ks.ks_phone);
						command.Parameters.AddWithValue("@cz", ks.ks_cz);
						command.Parameters.AddWithValue("@email", ks.ks_email);
						command.Parameters.AddWithValue("@address", ks.ks_address);
						command.Parameters.AddWithValue("@gj", ks.ks_gj);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 修改
		/// </summary>
		/// <param name="ks"></param>
		/// <returns></returns>
		public bool UpdateKS(KS ks)
		{
			string sql = @"update [dbo].[bd_ks] set
ks_name=@name, ks_type=@type, ks_jc=@jc, ks_level=@level, 
ks_sx=@sx, ks_hy=@hy, ks_person=@person, ks_phone=@phone, ks_cz=@cz, 
ks_email=@email, ks_address=@address, ks_gj=@gj,[ks_update_by]=1,[ks_update_time]=GETDATE()
where [ks_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@name", ks.ks_name);
						command.Parameters.AddWithValue("@type", ks.ks_type);
						command.Parameters.AddWithValue("@jc", ks.ks_jc);
						command.Parameters.AddWithValue("@level", ks.ks_level);
						command.Parameters.AddWithValue("@sx", ks.ks_sx);
						command.Parameters.AddWithValue("@hy", ks.ks_hy);
						command.Parameters.AddWithValue("@person", ks.ks_person);
						command.Parameters.AddWithValue("@phone", ks.ks_phone);
						command.Parameters.AddWithValue("@cz", ks.ks_cz);
						command.Parameters.AddWithValue("@email", ks.ks_email);
						command.Parameters.AddWithValue("@address", ks.ks_address);
						command.Parameters.AddWithValue("@gj", ks.ks_gj);
						command.Parameters.AddWithValue("@id", ks.ks_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 删除
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelKS(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_ks] 
                       SET ks_isdelete = 0 
                       WHERE ks_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 批量删除
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelKSs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_ks] 
		                                 SET ks_isdelete = 0 
		                                 WHERE ks_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}


		public string GetName(string ks_code)
		{
			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = $"select ks_name from bd_ks where ks_code='{ks_code}'";
				string name = connection.ExecuteScalar<string>(sql); // 使用 ToList() 获取所有菜单  
				return name;
			}
		}

        public List<KS> GetGYS()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "select [ks_code],[ks_name] from [dbo].[bd_ks]\r\nwhere [ks_type] =14 and [ks_isdelete]=1";
                return connection.Query<KS>(sql).ToList();
            }
        }


        public KS GetKSbycode(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select [ks_name] from [dbo].[bd_ks] where [ks_code]='{code}'";
                return connection.QueryFirstOrDefault<KS>(sql, new { code });
            }
        }

    }
}